﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DTO;
namespace DAO
{
    public class aspnet_UsersDAO
    {
        //load danh sach user
        public DataTable LoadDanhSachUser()
        {
            try
            {
                string sql = "select UserName, Email, rolename from aspnet_users u, aspnet_Membership m, aspnet_roles r, aspnet_UsersInRoles uir where u.userid = m.userid and u.UserId = uir.UserId and r.RoleId = uir.RoleId and (r.RoleName = 'staff' or r.RoleName = 'admin')";
                return Dataprovider.ExcuteQuery(sql);
            }
            catch
            {
                return null;
            }
        }
        //cap nhat user
        public int CapNhatUser(aspnet_UsersDTO user, aspnet_MembershipDTO member)
        {
            try
            {
                string sql = "update aspnet_Membership set email = '"+ member.Email +"' where UserId = (select UserId from aspnet_Users where UserName ='"+ user.UserName +"' )";
                return Dataprovider.ExcuteNonQuery(sql);
            }
            catch
            {
                return 0;
            }
        }
        //kiem tra email trùng
        public int KiemTraEmailTrung(string email)
        {
            try
            {
                string sql = "select COUNT(UserId) from aspnet_Membership where Email = '" + email + "'";
                return int.Parse(Dataprovider.ExcuteScalar(sql));
            }
            catch
            {
                return 0;
            }
        }
        //lay userid
        public string LayUserID(string taikhoan)
        {
            try
            {
                string sql = "select userid from aspnet_users where username ='" + taikhoan + "'";
                return Dataprovider.ExcuteScalar(sql);
            }
            catch
            {
                return null;
            }
        }
        //tim kiem nguoi dung
        public DataTable TimKiemNguoiDung(string username)
        {
            try
            {
                string sql = "select UserName, Email, rolename from aspnet_users u, aspnet_Membership m, aspnet_roles r, aspnet_UsersInRoles uir where u.userid = m.userid and u.UserId = uir.UserId and r.RoleId = uir.RoleId and (r.RoleName = 'staff' or r.RoleName = 'admin') and username like '%"+username+"%'";
                return Dataprovider.ExcuteQuery(sql);
            }
            catch
            {
                return null;
            }
        }
    }
}
